<?php 
/**
 * Cette page permet d'effectuer les requêtes SQL exécutées en AJAX sur la page whatsnew
 * Elle implémente le strict minimum de phpBB3 pour faire tourner ça, ce qui fait pas mal en fait
 * 
 * @author : Filament
 * @copyright : Disney Magic Interactive
 * @package : dMI V4
 * @name : getwhatsnew.php
 * @since 09/08/11
 * @version 0.1 (09/08/11) 
 **/


/**
 * Les inclusions nécessaires pour phpBB3 :
 */
//Chemin du répertoire courant à la racine
$rep ="../";

//design via phpBB3
define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : $rep.'phpBB3/';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);
include($phpbb_root_path . 'includes/bbcode.' . $phpEx);
require($phpbb_root_path . 'includes/functions_user.' . $phpEx);   
require($phpbb_root_path . 'includes/functions_display.' . $phpEx);
//Informations sur le membre :
$user->session_begin();
$auth->acl($user->data);

//Mise en page
$user->setup('viewforum');

//Déclarations essentielles pour le traitement de la page :
$bbcode_bitfield = '';
$bbcode = new bbcode(base64_encode($bbcode_bitfield));

/**
 * Ici on exécute les requêtes :
 */
//Avant tout le membre DOIT être connecté pour avoir le droit de se servir de cette page :
if ( !$user->data['is_registered'] )
{
    $data_result = "Vous devez &ecirc;tre connect&eacute; pour voir les derniers messages.";
} else {
	// Which forums should not be searched? Author searches are also carried out in unindexed forums
	$ex_fid_ary = array_unique(array_merge(array_keys($auth->acl_getf('!f_read', true)), array_keys($auth->acl_getf('!f_search', true))));
	
	
	$not_in_fid = (sizeof($ex_fid_ary)) ? 'WHERE ' . $db->sql_in_set('f.forum_id', $ex_fid_ary, true) . " OR (f.forum_password <> '' AND fa.user_id <> " . (int) $user->data['user_id'] . ')' : "";
	
	$sql = 'SELECT f.forum_id, f.forum_name, f.parent_id, f.forum_type, f.right_id, f.forum_password, f.forum_flags, fa.user_id
		FROM ' . FORUMS_TABLE . ' f
		LEFT JOIN ' . FORUMS_ACCESS_TABLE . " fa ON (fa.forum_id = f.forum_id
			AND fa.session_id = '" . $db->sql_escape($user->session_id) . "')
		$not_in_fid
		ORDER BY f.left_id";
	$result = $db->sql_query($sql);
	
	$right_id = 0;
	$reset_search_forum = true;
	while ($row = $db->sql_fetchrow($result))
	{
		if ($row['forum_password'] && $row['user_id'] != $user->data['user_id'])
		{
			$ex_fid_ary[] = (int) $row['forum_id'];
			continue;
		}
	}
	$db->sql_freeresult($result);
	
		
	// find out in which forums the user is allowed to view approved posts
	//Repris directement depuis search.php
	if ($auth->acl_get('m_approve'))
	{
		$m_approve_fid_ary = array(-1);
		$m_approve_fid_sql = '';
	}
	else if ($auth->acl_getf_global('m_approve'))
	{
		$m_approve_fid_ary = array_diff(array_keys($auth->acl_getf('!m_approve', true)), $ex_fid_ary);
		$m_approve_fid_sql = ' AND (p.post_approved = 1' . ((sizeof($m_approve_fid_ary)) ? ' OR ' . $db->sql_in_set('p.forum_id', $m_approve_fid_ary, true) : '') . ')';
	}
	else
	{
		$m_approve_fid_ary = array();
		$m_approve_fid_sql = ' AND p.post_approved = 1';
	}
	
	//Et selon le mod d'affichage demandé on n'utilise pas les  même fonctions :
	if(!empty($_POST['search']) && $_POST['search'] == 'all') {	
		//On récupère les messages non lus :
		$data_result = getMessagesNonLus ($m_approve_fid_sql, $ex_fid_ary);
	} else {
		//On récupère la liste de messages dans un sujet où j'ai posté :
		$data_result = getReponsesNonLues ($m_approve_fid_sql, $ex_fid_ary);
	}
}
/**
 * Et maintenant l'envoi du retour
 */
foreach($data_result as $text)
	echo $text;

/**
 * Il FAUT appeler le pied de page pour fermer la connexion à la bdd, sinon user_max_connection
 */
garbage_collection();
exit_handler();


function getMessagesNonLus ($m_approve_fid_sql, $ex_fid_ary) {
	global $db, $user, $rep;
		/*SELECT 
					t.topic_title
					, p.post_id
					, p.post_time
					, p.post_text
					, p.bbcode_bitfield
					, p.bbcode_uid
					, COUNT(*) as nb_posts
				FROM 
					' . POSTS_TABLE . ' p LEFT JOIN ' . TOPICS_TABLE . ' t
							ON p.topic_id = t.topic_id
				WHERE
						p.post_time > ' . $user->data['user_lastvisit'] . '
					AND
						p.poster_id <> ' . $user->data['user_id'] . '
						' . str_replace(array('p.', 'post_'), array('t.', 'topic_'), $m_approve_fid_sql) . '
						' . ((sizeof($ex_fid_ary)) ? 'AND ' . $db->sql_in_set('t.forum_id', $ex_fid_ary, true) : '') . '
				GROUP BY 
						p.topic_id
				UNION*/
	
	$sql = 'SELECT 
					t.topic_title
					, t.topic_id
					, t.forum_id
					, MAX(p.post_id) as post_id
					, MAX(p.post_time) as post_time
					, COUNT(*) as nb_posts
				FROM 
					' . POSTS_TABLE . ' p LEFT JOIN ' . TOPICS_TABLE . ' t
							ON p.topic_id = t.topic_id
					LEFT JOIN  ' . TOPICS_TRACK_TABLE . ' tt
							ON tt.user_id = ' . $user->data['user_id']. ' AND t.topic_id = tt.topic_id
						LEFT JOIN '. FORUMS_TRACK_TABLE . ' ft
							ON ft.user_id = ' . $user->data['user_id'] . ' AND t.forum_id = ft.forum_id
				WHERE
					(
						(tt.mark_time IS NOT NULL AND t.topic_last_post_time > tt.mark_time) OR
						(tt.mark_time IS NULL AND ft.mark_time IS NOT NULL AND t.topic_last_post_time > ft.mark_time) OR
						(tt.mark_time IS NULL AND ft.mark_time IS NULL AND t.topic_last_post_time > 0)
						) 
					AND
						p.poster_id <> ' . $user->data['user_id'] . '
					AND
						(p.post_time > tt.mark_time AND p.post_time > ft.mark_time)
						' . str_replace(array('p.', 'post_'), array('t.', 'topic_'), $m_approve_fid_sql) . '
						' . ((sizeof($ex_fid_ary)) ? 'AND ' . $db->sql_in_set('t.forum_id', $ex_fid_ary, true) : '') . '
					GROUP BY 
						p.topic_id 
					ORDER BY 
						MAX(p.post_time) 
						DESC
					LIMIT 0, 20';
	$result = $db->sql_query($sql);
	if($result->num_rows > 0) {
		$data_result = '';
		while($data = $db->sql_fetchrow($result)) {
			$infos_posters = "SELECT 
									u.username
									, u.user_id
									, u.user_colour
									, u.user_avatar
									, u.user_avatar_type
									, u.user_avatar_width
									, u.user_avatar_height
									, p.post_text
									, p.bbcode_bitfield
									, p.bbcode_uid
									
								FROM
									" . USERS_TABLE . " u INNER JOIN " . POSTS_TABLE . " p
										ON p.poster_id = u.user_id
								WHERE
									p.post_id = '" . $data['post_id'] . "'
								";
			$infos_result = $db->sql_query_limit($infos_posters, 1);
			$user_data = $db->sql_fetchrow($infos_result);
			$db->sql_freeresult($infos_result);
			
			//On encode le début du message du membre :
			$texte = smiley_text($user_data['post_text'], true);
			$texte = substr($texte, 0, 200).'...';
			$bbcode = new bbcode(base64_encode($user_data['bbcode_bitfield']));
			$bbcode->bbcode_second_pass($texte, $user_data['bbcode_uid'], $user_data['bbcode_bitfield']);
			$texte = bbcode_nl2br($texte);
				
			//Changeons momentanément le chemin vers phpBB3 :
			$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : 'phpBB3/';
			if($data['nb_posts'] == 1)
				$data_result[$data['post_time']] = '<div class="newCadre"><div class="newImage">' . get_user_avatar($user_data['user_avatar'], $user_data['user_avatar_type'], 50 , 50 / $user_data['user_avatar_width'] * $user_data['user_avatar_height']) . '</div><div class="newContent"> <p class="newsTitre"><a href="#">' . $data['topic_title'] . '</a></p><p class="newsInfos">' . $user->format_date($data['post_time']) . '</p><p class="newsText">' . $user_data['username'] . ' a &eacute;crit un message <br />
									<blockquote><br />'.$texte.'</blockquote><br />
									<a style="linkDiscussion" href="'.append_sid('phpBB3/viewtopic.php', 't='.$data['topic_id'] . '&amp;f=' . $data['forum_id'] .'&amp;view=unread#unread').'">Voir la suite de la discussion >></a>
								</p></div></div>';
			else
				$data_result[$data['post_time']] = '<div class="newCadre"><div class="newImage">' . get_user_avatar($user_data['user_avatar'], $user_data['user_avatar_type'], 50 , 50 / $user_data['user_avatar_width'] * $user_data['user_avatar_height']) . '</div><div class="newContent"> <p class="newsTitre"><a href="#">' . $data['topic_title'] . '</a></p><p class="newsInfos">' . $user->format_date($data['post_time']) . '</p><p class="newsText">' . $user_data['username'] . ' et d\'autres ont &eacute;crit ' .  $data['nb_posts'] . ' message <br />
									<blockquote><br />'.$texte.'</blockquote><br />
									<a style="linkDiscussion" href="'.append_sid('phpBB3/viewtopic.php', 't='.$data['topic_id'] . '&amp;f=' . $data['forum_id'] .'&amp;view=unread#unread').'">Voir la suite de la discussion >></a>
								</p></div></div>';
			//Remettons le chemin vers phpBB3 :
			$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : $rep . 'phpBB3/';
		}
	}
	else
		$data_result = 'Rien de nouveau...';

	$db->sql_freeresult($result);
	return $data_result;
}

function getReponsesNonLues ($m_approve_fid_sql, $ex_fid_ary) {
	global $db, $user, $rep;
		/*SELECT 
					t.topic_title
					, p.post_id
					, p.post_time
					, p.post_text
					, p.bbcode_bitfield
					, p.bbcode_uid
					, COUNT(*) as nb_posts
				FROM 
					' . POSTS_TABLE . ' p LEFT JOIN ' . TOPICS_TABLE . ' t
							ON p.topic_id = t.topic_id
				WHERE
						p.post_time > ' . $user->data['user_lastvisit'] . '
					AND
						p.poster_id <> ' . $user->data['user_id'] . '
						' . str_replace(array('p.', 'post_'), array('t.', 'topic_'), $m_approve_fid_sql) . '
						' . ((sizeof($ex_fid_ary)) ? 'AND ' . $db->sql_in_set('t.forum_id', $ex_fid_ary, true) : '') . '
				GROUP BY 
						p.topic_id
				UNION*/
	
	$sql = 'SELECT 
					t.topic_title
					, t.topic_id
					, t.forum_id
					, MAX(p.post_id) as post_id
					, MAX(p.post_time) as post_time
					, COUNT(*) as nb_posts
				FROM 
					' . TOPICS_POSTED_TABLE . ' tp ,
					' . ZEBRA_TABLE . ' z,
					' . POSTS_TABLE . ' p LEFT JOIN ' . TOPICS_TABLE . ' t
							ON p.topic_id = t.topic_id
					LEFT JOIN  ' . TOPICS_TRACK_TABLE . ' tt
						ON tt.user_id = ' . $user->data['user_id']. ' AND t.topic_id = tt.topic_id
					LEFT JOIN '. FORUMS_TRACK_TABLE . ' ft
						ON ft.user_id = ' . $user->data['user_id'] . ' AND t.forum_id = ft.forum_id
				WHERE
					(
						(tt.mark_time IS NOT NULL AND t.topic_last_post_time > tt.mark_time) OR
						(tt.mark_time IS NULL AND ft.mark_time IS NOT NULL AND t.topic_last_post_time > ft.mark_time) OR
						(tt.mark_time IS NULL AND ft.mark_time IS NULL AND t.topic_last_post_time > 0)
						) 
					AND
						p.poster_id <> ' . $user->data['user_id'] . '
					AND
						(
							(
									p.topic_id = tp.topic_id
								AND
									tp.user_id = ' . $user->data['user_id'] . '
								AND
									tp.topic_posted = 1
							) 
							OR
							(
									tp.user_id = z.zebra_id
								AND	
									z.user_id = ' . $user->data['user_id'] . '
								AND
									z.friend = 1
								AND
									p.topic_id = tp.topic_id
								AND
									tp.topic_posted = 1
							)
						)
						' . str_replace(array('p.', 'post_'), array('t.', 'topic_'), $m_approve_fid_sql) . '
						' . ((sizeof($ex_fid_ary)) ? 'AND ' . $db->sql_in_set('t.forum_id', $ex_fid_ary, true) : '') . '
					GROUP BY 
						p.topic_id 
					ORDER BY 
						MAX(p.post_time) 
						DESC
					LIMIT 0, 20';
	$result = $db->sql_query($sql);
	if($result->num_rows > 0) {
		$data_result = '';
		while($data = $db->sql_fetchrow($result)) {
			$infos_posters = "SELECT 
									u.username
									, u.user_id
									, u.user_colour
									, u.user_avatar
									, u.user_avatar_type
									, u.user_avatar_width
									, u.user_avatar_height
									, p.post_text
									, p.bbcode_bitfield
									, p.bbcode_uid
									
								FROM
									" . USERS_TABLE . " u INNER JOIN " . POSTS_TABLE . " p
										ON p.poster_id = u.user_id
								WHERE
									p.post_id = '" . $data['post_id'] . "'
								";
			$infos_result = $db->sql_query_limit($infos_posters, 1);
			$user_data = $db->sql_fetchrow($infos_result);
			$db->sql_freeresult($infos_result);
			
			//On encode le début du message du membre :
			$texte = smiley_text($user_data['post_text'], true);
			$texte = substr($texte, 0, 200).'...';
			$bbcode = new bbcode(base64_encode($user_data['bbcode_bitfield']));
			$bbcode->bbcode_second_pass($texte, $user_data['bbcode_uid'], $user_data['bbcode_bitfield']);
			$texte = bbcode_nl2br($texte);
				
			//Changeons momentanément le chemin vers phpBB3 :
			$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : 'phpBB3/';
			if($data['nb_posts'] == 1)
				$data_result[$data['post_time']] = '<div class="newCadre"><div class="newImage">' . get_user_avatar($user_data['user_avatar'], $user_data['user_avatar_type'], 50 , 50 / $user_data['user_avatar_width'] * $user_data['user_avatar_height']) . '</div><div class="newContent"> <p class="newsTitre"><a href="#">' . $data['topic_title'] . '</a></p><p class="newsInfos">' . $user->format_date($data['post_time']) . '</p><p class="newsText">' . $user_data['username'] . ' Vous a r&eacute;pondu. <br />
									<blockquote><br />'.$texte.'</blockquote><br />
									<a style="linkDiscussion" href="'.append_sid('phpBB3/viewtopic.php', 't='.$data['topic_id'] . '&amp;f=' . $data['forum_id'] .'&amp;view=unread#unread').'">Voir la suite de la discussion >></a>
								</p></div></div>';
			else
				$data_result[$data['post_time']] = '<div class="newCadre"><div class="newImage">' . get_user_avatar($user_data['user_avatar'], $user_data['user_avatar_type'], 50 , 50 / $user_data['user_avatar_width'] * $user_data['user_avatar_height']) . '</div><div class="newContent"> <p class="newsTitre"><a href="#">' . $data['topic_title'] . '</a></p><p class="newsInfos">' . $user->format_date($data['post_time']) . '</p><p class="newsText">' . $user_data['username'] . ' et d\'autres vous ont r&eacute;pondu ' .  $data['nb_posts'] . ' message <br />
									<blockquote><br />'.$texte.'</blockquote><br />
									<a style="linkDiscussion" href="'.append_sid('phpBB3/viewtopic.php', 't='.$data['topic_id'] . '&amp;f=' . $data['forum_id'] .'&amp;view=unread#unread').'">Voir la suite de la discussion >></a>
								</p></div></div>';
			//Remettons le chemin vers phpBB3 :
			$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : $rep . 'phpBB3/';
		}
	}
	else
		$data_result = 'Rien de nouveau...';

	$db->sql_freeresult($result);
	return $data_result;
}
?>